package cn.nubia.admin.common.mybatis.provider;


import cn.nubia.admin.common.mybatis.annotations.Id;
import cn.nubia.admin.common.mybatis.annotations.SortColumn;
import cn.nubia.admin.common.mybatis.annotations.UnColumn;
import cn.nubia.admin.common.mybatis.bean.QueryParam;
import cn.nubia.admin.common.mybatis.bean.SqlQuery;
import cn.nubia.admin.common.mybatis.util.ClassUtil;
import cn.nubia.admin.common.mybatis.util.ColumnUtils;
import cn.nubia.admin.common.mybatis.util.ObjectUtil;
import cn.nubia.admin.common.mybatis.annotations.*;
import cn.nubia.admin.common.mybatis.bean.Query;
import cn.nubia.admin.common.mybatis.bean.QueryParam;
import cn.nubia.admin.common.mybatis.bean.SqlQuery;
import cn.nubia.admin.common.mybatis.util.ClassUtil;
import cn.nubia.admin.common.mybatis.util.ColumnUtils;
import cn.nubia.admin.common.mybatis.util.ObjectUtil;
import org.apache.commons.lang3.StringUtils;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.jdbc.SQL;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.apache.commons.beanutils.BeanUtils;

import java.lang.reflect.Field;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.List;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

/**
 * 默认的CRUD
 * User: wangj
 * Date: 13-11-8
 * Time: 上午9:32
 */
public class CrudProvider<T> {
    private static final Logger logger = LoggerFactory.getLogger(CrudProvider.class);


    /* --------------------------------------- SqlQuery 对象部分 ------------------------*/
    public String findBySqlQuery(SqlQuery query) {
        String sql = query.getSql();
        sql = addParamPrefix(sql, SqlQuery.PARAMS_FIELD_NAME, query.getParams());
        if (query.getOffset() >= 0) {
            sql = sql + " limit #{offset},#{pageSize}";
        }
        if (logger.isDebugEnabled()) {
            logger.debug("sql is:" + sql);
        }
        return sql;
    }

    public String countBySqlQuery(SqlQuery sqlQuery) {
        String sql = sqlQuery.getSql().toLowerCase();


        //去除select xxx 到 from的部分
        int fromIndex = sql.indexOf("from");
        if (fromIndex != -1) {
            sql = sql.substring(fromIndex);
        }
        if (StringUtils.isNotEmpty(sqlQuery.getDistinctParam())) {
            sql = "select count(distinct " + sqlQuery.getDistinctParam() + ")" + sql;
        } else {
            sql = "select count(*) " + sql;
        }

        //去除group部分
        int groupIndex = sql.indexOf("group by");
        if (groupIndex != -1) {
            sql = sql.substring(0, groupIndex);
        }

        //去除limit部分
        int limitIndex = sql.indexOf("limit");
        if (limitIndex != -1) {
            sql = sql.substring(0, limitIndex);
        }
        sql = addParamPrefix(sql, SqlQuery.PARAMS_FIELD_NAME, sqlQuery.getParams());
        if (logger.isDebugEnabled()) {
            logger.debug("sql is:" + sql);
        }
        return sql;
    }




    /* --------------------------------------- Query 对象部分 ------------------------*/

    /**
     * 执行查询
     *
     * @param query
     * @return
     * @throws Exception
     */
    public String findByQuery(Query<T> query) throws Exception {
        return findPageByObject(query);
    }


    /**
     * 执行删除操作
     */
    public String deleteByQuery(Query<T> query) throws Exception {

        SQL sql = new SQL();
        sql.DELETE_FROM(obtainTableNameInQuery(query));

        List<String> whereConditions = buildWhereCommands(query);
        if (whereConditions.size() == 0) {
            throw new RuntimeException("condition is empty,can't delete");
        }

        for (String str : whereConditions) {
            sql.WHERE(str);
        }
        if (logger.isDebugEnabled()) {
            logger.debug("sql is:" + sql.toString());
        }
        return sql.toString();
    }

    /**
     * 执行统计,用于分页
     *
     * @param query
     * @return
     * @throws Exception
     */


    /**
     * 构造条件子句
     *
     * @param query
     * @return
     * @throws Exception
     */
    private List<String> buildWhereCommands(Query<T> query) throws Exception {
        List<String> list = new ArrayList<String>();

        int i = 0;
        for (QueryParam param : query.getParamList()) {
            Field field = ClassUtil.getField(query.getType(), param.fieldName);
            if (field == null) {
                continue;
            }
            String columnName = getColumnName(field);
            String expression = param.getExpression();
            StringBuilder sb = new StringBuilder();
            sb.append(columnName);
            sb.append(" ").append(expression).append(" ");

            switch (param.type) {
                case Query.PARAM_BETWEEN:
                    sb.append("#{").append(Query.PARAM_NAME).append("[").append(i).append("]").append(".value.begin").append("}");
                    sb.append(" ").append("and").append(" ");
                    sb.append("#{").append(Query.PARAM_NAME).append("[").append(i).append("]").append(".value.end").append("}");
                    break;
                case Query.PARAM_IN: {
                    sb.append(" (");

                    List<Object> objects;
                    if (param.value instanceof List) {
                        objects = (List<Object>) param.value;
                    } else {
                        throw new RuntimeException("value not List");
                    }

                    StringBuilder innerStr = new StringBuilder();
                    for (int j = 0; j < objects.size(); j++) {
                        innerStr.append("#{").append(Query.PARAM_NAME).append("[").append(i).append("]").append(".value[").append(j).append("]}").append(",");
                    }
                    sb.append(innerStr.substring(0, innerStr.length() - 1)).append(")");
                }
                break;
                case Query.PARAM_NOT_IN: {
                    sb.append(" (");
                    List<Object> objects;
                    if (param.value instanceof List) {
                        objects = (List<Object>) param.value;
                    } else {
                        throw new RuntimeException("value not List");
                    }

                    StringBuilder innerStr = new StringBuilder();
                    for (int j = 0; j < objects.size(); j++) {
                        innerStr.append("#{").append(Query.PARAM_NAME).append("[").append(i).append("]").append(".value[").append(j).append("]}").append(",");
                    }
                    sb.append(innerStr.substring(0, innerStr.length() - 1)).append(")");
                }
                break;
                case Query.PARAM_SUB_QUERY_IN: {

                    String subQuery;
                    if (param.value instanceof String) {
                        subQuery = (String) param.value;
                    } else {
                        throw new RuntimeException("sub query must be a sql");
                    }
                    StringBuilder innerBuilder = new StringBuilder();
                    innerBuilder.append(Query.PARAM_NAME).append("[").append(i).append("]").append(".subQueryData");

                    sb.append(" (");
                    String innerSql = addParamPrefix(subQuery, innerBuilder.toString(), param.subQueryData);
                    sb.append(innerSql).append(")");
                }
                break;
                case Query.PARAM_SUB_QUERY_LIKE: {
                    String subQuery;
                    if (param.value instanceof String) {
                        subQuery = (String) param.value;
                    } else {
                        throw new RuntimeException("sub query must be a sql");
                    }
                    StringBuilder innerBuilder = new StringBuilder();
                    innerBuilder.append(Query.PARAM_NAME).append("[").append(i).append("]").append(".subQueryData");

                    sb.append(" (");
                    String innerSql = addParamPrefix(subQuery, innerBuilder.toString(), param.subQueryData);
                    sb.append(innerSql).append(")");
                }
                break;

                default:
                    sb.append("#{").append(Query.PARAM_NAME).append("[").append(i).append("]").append(".value").append("}");
            }

            list.add(sb.toString());
            i++;
        }

        return list;
    }

    final static Pattern pattern = Pattern.compile("#\\{(\\w+)\\}");

    private String addParamPrefix(String subQuery, String prefix, List<Object> list) {
        StringBuffer sb = new StringBuffer();
        Matcher matcher = pattern.matcher(subQuery);

        int paramNum = 0;
        while (matcher.find()) {
            StringBuilder builder = new StringBuilder();
            builder.append("#{");
            builder.append(prefix).append("[").append(paramNum).append("]");
            builder.append("}");

            matcher.appendReplacement(sb, builder.toString());
            paramNum++;
        }
        if (paramNum != list.size()) {
            throw new RuntimeException("传入参数个数不正确,需要不为空参数个数为:" + paramNum);
        }

        matcher.appendTail(sb);
        return sb.toString();
    }

    public static void main(String[] args) {
        CrudProvider crudProvider = new CrudProvider();

        SqlQuery sqlQuery = new SqlQuery("select * from t_data where id = #{id} limit 1,10", new Object[]{10});
        System.out.println(crudProvider.countBySqlQuery(sqlQuery));
    }


    /**
     * 计数
     *
     * @param query
     * @return
     * @throws Exception
     */
    public String count(Query<T> query) throws Exception {
        SQL sql = new SQL();

        sql.SELECT("count(1)");
        sql.FROM(obtainTableNameInQuery(query));

        for (String str : buildWhereCommands(query)) {
            sql.WHERE(str);
        }

        if (logger.isDebugEnabled()) {
            logger.debug(sql.toString());
        }
        return sql.toString();
    }

    /**
     * 查询对象
     *
     * @return
     * @throws Exception
     */
    public String findPageByObject(Query<T> query) throws Exception {
        SQL sql = new SQL();

        StringBuilder cols = new StringBuilder();
        Field[] fields = query.getType().getDeclaredFields();
        for (Field field : fields) {
            if (!ClassUtil.isProperty(query.getType(), field.getName())) {
                continue;
            }

            if (field.getAnnotation(UnColumn.class) != null) {
                continue;
            }


            if (!query.hasOrder()) {
                String sortField = getSortColumnName(field);
                if (sortField != null) {
                    sql.ORDER_BY(sortField + " DESC");
                }
            }

            if (query.isSearchAllField() || query.isFieldSearch(field.getName())) {
                String columnName = getColumnName(field);
                cols.append(columnName).append(" as ").append(field.getName()).append(",");
            }

        }
        for (Map.Entry<String, Query.DBOrder> sort : query.getOrders().entrySet()) {
            Field field = query.getType().getDeclaredField(sort.getKey());
            if (field != null) {
                Query.DBOrder dbOrder = sort.getValue();
                if (dbOrder != null) {
                    sql.ORDER_BY(getColumnName(field) + " " + dbOrder.getName());
                }
            }
        }
        sql.SELECT(cols.toString().substring(0, cols.length() - 1));
        sql.FROM(obtainTableNameInQuery(query));

        for (String str : buildWhereCommands(query)) {
            sql.WHERE(str);
        }

        String sqlCommand;
        if (query.getOffset() >= 0) {
            sqlCommand = sql.toString() + " limit #{offset},#{pageSize}";
        } else {
            sqlCommand = sql.toString();
        }
        if (logger.isDebugEnabled()) {
            logger.debug(sqlCommand);
        }
        return sqlCommand;
    }


    /**
     * 保存对象
     *
     * @param obj
     * @return
     * @throws Exception
     */
    public String save(T obj) throws Exception {
        String sql;
        if (!isIdExist(obj)) {
            sql = insert(obj);
        } else {
            sql = update(obj);
        }
        return sql;
    }

    /**
     * 更新对象
     *
     * @param obj
     * @return
     * @throws Exception
     */
    public String update(T obj) throws Exception {
        SQL sql = new SQL();
        sql.UPDATE(obtainTableName(obj));

        String idName = "";
        String idFieldName = "";
        Field[] fields = obj.getClass().getDeclaredFields();
        for (Field field : fields) {
            Id idField = field.getAnnotation(Id.class);
            if (idField != null) {
                idName = idField.value();
                idFieldName = field.getName();
                continue;
            }
            if (skipField(obj, field)) {
                continue;
            }

            sql.SET(getColumnName(field) + "=#{" + field.getName() + "}");
        }

        sql.WHERE(idName + "=#{" + idFieldName + "}");

        if (logger.isDebugEnabled()) {
            logger.debug(sql.toString());
        }
        return sql.toString();
    }

    /**
     * 删除对象
     *
     * @param obj
     * @return
     * @throws Exception
     */
    public String insert(T obj) throws Exception {
        SQL sql = new SQL();
        sql.INSERT_INTO(obtainTableName(obj));
        StringBuilder cols = new StringBuilder();
        StringBuilder values = new StringBuilder();
        Field[] fields = obj.getClass().getDeclaredFields();
        for (Field field : fields) {
            if (skipField(obj, field)) {
                continue;
            }

            values.append("#{").append(field.getName()).append("},");
            cols.append(getColumnName(field)).append(",");
        }

        sql.VALUES(cols.toString().substring(0, cols.length() - 1),
                values.toString().substring(0, values.length() - 1));
        if (logger.isDebugEnabled()) {
            logger.debug(sql.toString());
        }
        return sql.toString();
    }

    public <M> String getColName(Class<M> clazz, String fieldName) {
        for (Field field : clazz.getDeclaredFields()) {
            String columnName = getColumnName(field);
            if (fieldName.equals(field.getName())) {
                return columnName;
            }
        }
        throw new RuntimeException("fieldName :" + fieldName + " not find ");
    }


    private String getColumnName(Field field) {
        Column column = field.getAnnotation(Column.class);
        SortColumn sortColumn = field.getAnnotation(SortColumn.class);
        Id idField = field.getAnnotation(Id.class);
        if (column != null && !StringUtils.isEmpty(column.value())) {
            return column.value();
        }
        if (sortColumn != null && !StringUtils.isEmpty(sortColumn.value())) {
            return sortColumn.value();
        } else if (idField != null && !StringUtils.isEmpty(idField.value())) {
            return idField.value();
        } else {
            return ObjectUtil.toDbField(field.getName());
        }
    }

    private String getSortColumnName(Field field) {
        SortColumn sortColumn = field.getAnnotation(SortColumn.class);
        if (sortColumn != null) {
            if (StringUtils.isEmpty(sortColumn.value())) {
                return field.getName();
            } else {
                return sortColumn.value();
            }
        }
        return null;
    }


    private boolean isIdExist(T obj) {
        for (Field field : obj.getClass().getDeclaredFields()) {
            Id idField = field.getAnnotation(Id.class);
            if (idField != null) {
                try {
                    String value = BeanUtils.getProperty(obj, field.getName());
                    if (value != null) {
                        return true;
                    }
                } catch (Exception e) {
                    logger.error("get id prop error!", e);
                }
            }
        }
        return false;
    }

    public String obtainTableNameInQuery(Query<T> query) {
        Table table = query.getType().getAnnotation(Table.class);
        if (table != null) {
            if (!table.isShard()) {
                return table.value();
            }
            String shardParamName = table.shardParam();
            if (shardParamName == null) {
                throw new RuntimeException("sharad param not set");
            }
            Object value = null;
            for (QueryParam queryParam : query.getParamList()) {
                if (queryParam.fieldName.equals(shardParamName)) {
                    value = queryParam.value;
                }
            }
            return ColumnUtils.getHashedTableName(table, value);
        } else {
            throw new RuntimeException("undefine POJO @Table, need Tablename(@Table)");
        }
    }


    public String obtainTableName(Object object) {
        Table table = object.getClass().getAnnotation(Table.class);
        if (table != null) {
            if(table.splitYear()){
                return table.value()+"_"+getYEAR();
            }
            if (!table.isShard()) {
                return table.value();
            }
            //判断是否是分片的.
            Field fields[] = object.getClass().getDeclaredFields();
            Object value = null;
            Field.setAccessible(fields, true);
            for (int i = 0; i < fields.length; i++) {
                if (fields[i].getName().equalsIgnoreCase(table.shardParam())) {
                    try {
                        value = fields[i].get(object);
                        break;
                    } catch (IllegalAccessException e) {
                        throw new RuntimeException("undefine POJO @Table, need Tablename(@Table)");
                    }
                }
            }
            return ColumnUtils.getHashedTableName(table, value);
        } else {
            throw new RuntimeException("undefine POJO @Table, need Tablename(@Table)");
        }
    }

    /**
     * 获取表名
     *
     * @param clazz
     * @param value 需要做hash的对象值
     * @return
     */
    public String obtainTableName(Class<T> clazz, Object value) {
        Table table = clazz.getAnnotation(Table.class);
        if (table != null) {
            if (!table.isShard()) {
                return table.value();
            }
            return ColumnUtils.getHashedTableName(table, value);
        } else {
            throw new RuntimeException("undefine POJO @Table, need Tablename(@Table)");
        }
    }

    /**
     * 获取POJO对应的主键名称
     * 需要POJO中的属性定义@Id
     *
     * @return
     */
    public String obtainIdName(Class<?> clazz) {
        for (Field field : clazz.getFields()) {
            if (field.isAnnotationPresent(Id.class))
                return field.getName();
        }
        return null;
    }

    private boolean skipField(T obj, Field field) {
        try {
            UnColumn unColumn = field.getAnnotation(UnColumn.class);
            if (unColumn != null) {
                return true;
            }

            if (!ClassUtil.isProperty(obj.getClass(), field.getName())) {
                return true;
            }

            String value = BeanUtils.getProperty(obj, field.getName());
            if (value == null) {
                return true;
            }


        } catch (Exception e) {
            e.printStackTrace();
        }
        return false;
    }


    public String getYEAR(){
        Calendar c = Calendar.getInstance();
        c.setTimeInMillis(System.currentTimeMillis());
        return String.valueOf(c.get(Calendar.YEAR));
    }
}
